#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import sys
import ibm_db
import pandas as pd
import numpy as np
import db_utils as util
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool


def main(p_argv=None):
    # 建立数据库连接
    conn_rds = util.getConnectionDb2('11.11.11.11', '11', '11', '11', '11')
    conn_db2 = util.getConnectionDb2('11.11.11.11', '11 ', '11', '11', '11')
    # Q402
    # 读取上一次插入的时间
    # TODO 改成当月的第一天0点0分0秒
    # start_time1 = (datetime.datetime.now() + datetime.timedelta(days=-40)).strftime('%Y%m%d%H%M%S')
    now = datetime.datetime.now()
    start_time1 = datetime.datetime(now.year, now.month, 1).strftime('%Y%m%d%H%M%S')
    end_time1 = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    sql1 = "select MAX(REC_CREATE_TIME) as LAST_CREATE_TIME_MAX from BGTARSSI00.T_DWD_FACT_ZZSCSI_ZHPJ_CP where UNIT_CODE='Q402'"
    success, results1 = fetchall_sql(sql1, conn_rds)
    if success and len(results1) > 0 and results1[0]['LAST_CREATE_TIME_MAX'] is not None:
        start_time1 = results1[0]['LAST_CREATE_TIME_MAX']

    # 从DB2数据库取CP符合率数据
    sql2 = "select REC_CREATE_TIME ,CURVE_CODE as item_id,remark_2 as ITEM_CHN_NAME, UNIT_CODE,MAT_NO as PROD_COILNO, IN_MAT_NO as ENTRY_COILNO, SEG_NAME  as SECTION_NAME, cast(replace(JUDGE_RULE_VALUE,'%','') as decimal(5,2))  as QUALIFIED_RATE, int(remark_6) as QUALIFIED_QTY, int(remark_7) as TOTAL_QTY, case when replace(JUDGE_RULE_VALUE,'%','')>=90 then 1 else 0 end as QUALIFIED_JUDGE,case when MATCH_SIGN_CODE in('Between','>=','>') then cast(BGRASIDS.SPLIT_UL(JUDGE_VALUE_AIM,1) as decimal(8,2)) end as STD_MIN, case when MATCH_SIGN_CODE in('Between','<=','<') then cast(BGRASIDS.SPLIT_UL(JUDGE_VALUE_AIM,2) as decimal(8,2)) end as STD_MAX from QMZJJ4.TQMZJJ4LG WHERE REMARK_4=0 AND UNIT_CODE ='Q402' AND remark_7 !='' AND REC_CREATE_TIME >=%s AND REC_CREATE_TIME <=%s" % (
        start_time1, end_time1)
    cp_data1 = util.query(conn_db2, sql2)
    # 从RDS的Q402机组基表取生产结束时间（包括生产月份、生产日期）、钢卷出口重量
    sql3 = 'select PRD_COILNO as PROD_COILNO,END_TIME,P_WEIGHT as exit_wt,STEEL_GRADE as TAPPING_MARK,substr(end_time,1,8) as PROD_DATE, substr(end_time,1,6) as PROD_MONTH,turn as PROD_TURN from BGTAROQ402.Q402_BASE'
    base1 = util.query(conn_rds, sql3)
    # 将两个df合并
    dataLfDf = pd.merge(cp_data1, base1, on='prod_coilno', how='left')
    pd.set_option('display.max_columns', None)
    res = dataLfDf[
        ['item_id', 'item_chn_name', 'end_time', 'unit_code', 'prod_coilno', 'entry_coilno', 'section_name', 'exit_wt',
         'tapping_mark', 'qualified_rate', 'qualified_qty', 'total_qty', 'qualified_judge', 'prod_date', 'prod_month',
         'prod_turn', 'std_min', 'std_max', 'rec_create_time']]
    res.to_sql('t_dwd_fact_zzscsi_zhpj_cp', con=conn_rds, schema='bgtarssi00', index=False, if_exists='append')

    # util.closeConnection(conn_db2)
    # util.closeConnection(conn_rds)

    return 0


def fetchall_sql(p_sql: str = None, p_conn=None):
    """
    # NOTE https://blog.csdn.net/weixin_42970378/article/details/85564462

    SQL语句结果返回
    |-------------------------------|----------------------------------------------------------------
    |语句      	                    |描述
    |-------------------------------|----------------------------------------------------------------
    |ibm_db.fetch_tuple（）	        |返回按列位置索引的元组，表示结果集中的行。列是0索引的
    |-------------------------------|----------------------------------------------------------------
    |ibm_db.fetch_assoc（）	        |返回按列名索引的字典，表示结果集中的行
    |-------------------------------|----------------------------------------------------------------
    |ibm_db.fetch_both（）	        |返回一个字典，由列名和位置索引，表示结果集中的行
    |-------------------------------|----------------------------------------------------------------
    |ibm_db.fetch_row（）	        |将结果集指针设置为下一行或请求的行。使用此函数迭代结果集
    |-------------------------------|----------------------------------------------------------------

    import ibm_db
    db_connect = ibm_db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username;PWD=password;", "", "")
    sql = "SELECT * FROM EMPLOYEE"
    stmt = ibm_db.exec_immediate(db_connect, sql)
    #
    # 通过调用ibm_db.fetch_tuple（）函数从结果集中获取行。
    tuple = ibm_db.fetch_tuple(stmt)
    while tuple != False:
        print "The ID is : ", tuple[0]
        print "The name is : ", tuple[1]
    #
    # 通过调用ibm_db.fetch_assoc（）函数从结果集中根据字典的键获取结果
    dictionary = ibm_db.fetch_assoc(stmt)
    while dictionary != False:
        print "The ID is : ", dictionary["EMPNO"]
    #
    # 通过调用ibm_db.fetch_both（）函数从结果集中获取行的结果或根据字典的键获取结果
    dictionary = ibm_db.fetch_both(stmt)
    while dictionary != False:
        print "The ID is : ",  dictionary["EMPNO"]
        print "The Name is : ", dictionary[1]
    #
    # 通过调用ibm_db.fetch_row（）函数从结果集中进行迭代获取结果
    while ibm_db.fetch_row(stmt) != False:
        print("The Employee number is : ",  ibm_db.result(stmt, 0))
        print("The Name is : ", ibm_db.result(stmt, "NAME"))
    #
    :param p_sql:
    :param p_conn:
    :return:
    """
    print(p_sql)
    success = False
    results = list()
    try:
        stmt = ibm_db.exec_immediate(p_conn, p_sql)
        dictionary = ibm_db.fetch_both(stmt)
        # NOTE ibm_db没办法一次提取多行,提取多行数据,需要循环提取
        while dictionary != False:
            results.append(dictionary)
            dictionary = ibm_db.fetch_both(stmt)
        success = True
    except Exception as e:
        success = False
        results = list()
        import traceback
        traceback.print_exc()
    return success, results


if __name__ == '__main__':
    start = datetime.datetime.now()

    status = main(sys.argv)

    elapsed = float((datetime.datetime.now() - start).seconds)
    print("Time Used 4 All ----->>>> %f seconds" % (elapsed))

    sys.exit(status)
